﻿

using System;
using System.Collections.Generic;

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Profile;
using System.Data;
using System.Text;
using Gift.Model.ENUM;

using Gift.DAL;
using Gift.Model;
using Gift.Utile;

namespace WebSite.admin.Report
{
    public partial class CardReport : BaseAdmin
    {


        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = this.ddlCurrentPage.SelectedIndex;
            this.GridViewBind("");
        }

        private string GetSql()
        {
            if (this.ddlCompany.SelectedItem.Value == "")
            {
                return GetSqlForAllCompany();
            }
            StringBuilder sbSql = new StringBuilder();
            string sSql = "select  p.[name] as 公司名称,"
                               +" ct.[name] as 卡片名称, "
                               +" c.CardPrice as 面额, count(p.[name]) as 领用数, "
                               +" sum(case when c.states in (1,5) and  convert(datetime, convert(varchar(10),getdate(),121))  <= convert(datetime, convert(varchar(10), endtime,121)) "
                               +" then 1 else 0 end ) as 激活数,"
                               + "  sum(case when c.states =4   then 1 else 0 end ) as 未激活数,";
            if(base.CompanyId == null)
            {
                sSql = sSql + "  sum(case when c.states =2   then 1 else 0 end ) as 已使用数,"
                               + " sum(case when c.states in (1,5) and convert(datetime, convert(varchar(10), getdate(),121))  > convert(datetime, convert(varchar(10), lasttime,121)) "
                               + " then 1 else 0 end ) as 已失效数,"
                               + " sum(case when c.states in (1,5) and  convert(datetime, convert(varchar(10), getdate(),121))  > convert(datetime, convert(varchar(10), endtime,121))  "
                               + "  and convert(datetime, convert(varchar(10), getdate(),121))  <= convert(datetime, convert(varchar(10), lasttime,121)) "
                               + " then 1 else 0 end ) as 礼品商城数,";
                             
                              
            }

            sSql = sSql + " sum(case when c.states =3   then 1 else 0 end ) as 被禁用数"
                               +" from db_Card c inner join tbCardType ct on c.CardTypeId = ct.id {0} {3} join  tbProfessionAreaType pt on ct.ProfessionAreaTypeId=pt.id {1}"
                               +" join tbCompany p on c.companyId = p.id {2} "
                               +" group by p.[name],ct.[name],p.id,c.CardPrice,ct.id order by p.[id],p.[name]";

            sbSql.AppendFormat(sSql

                          , this.ddlCardType.SelectedItem.Value == "" ? "" : " and ct.id = " + this.ddlCardType.SelectedItem.Value
                          , this.ddlProfessionAreaType.SelectedItem.Value == "" ? "" : "and pt.id = " + this.ddlProfessionAreaType.SelectedItem.Value
                          , this.ddlCompany.SelectedItem.Value == "" ? "" : "and c.companyid =  " + this.ddlCompany.SelectedItem.Value
                          , string.IsNullOrEmpty(this.txtBatchNumber.Text.Trim()) ? "" : " and c.cardcode like '" + this.txtBatchNumber.Text.Trim() + "%'"
                            );

            return sbSql.ToString(); ;
        }

        private string GetSqlForAllCompany()
        {
            StringBuilder sbSql = new StringBuilder();
            string sSql = "select  "
                               + " ct.[name] as 卡片名称, "
                               + " c.CardPrice as 面额, count(ct.[name]) as 领用数, "
                               + " sum(case when c.states in (1,5) and  convert(datetime, convert(varchar(10),getdate(),121))  <= convert(datetime, convert(varchar(10), endtime,121)) "
                               + " then 1 else 0 end ) as 激活数,"
                               + "  sum(case when c.states =4   then 1 else 0 end ) as 未激活数,";
            if (base.CompanyId == null)
            {
                sSql = sSql + "  sum(case when c.states =2   then 1 else 0 end ) as 已使用数,"
                               + " sum(case when c.states in (1,5) and convert(datetime, convert(varchar(10), getdate(),121))  > convert(datetime, convert(varchar(10), lasttime,121)) "
                               + " then 1 else 0 end ) as 已失效数,"
                               + " sum(case when c.states in (1,5) and  convert(datetime, convert(varchar(10), getdate(),121))  > convert(datetime, convert(varchar(10), endtime,121))  "
                               + "  and convert(datetime, convert(varchar(10), getdate(),121))  <= convert(datetime, convert(varchar(10), lasttime,121)) "
                               + " then 1 else 0 end ) as 礼品商城数,";


            }

            sSql = sSql + " sum(case when c.states =3   then 1 else 0 end ) as 被禁用数"
                               + " from db_Card c inner join tbCardType ct on c.CardTypeId = ct.id {0} {3} join  tbProfessionAreaType pt on ct.ProfessionAreaTypeId=pt.id {1}"
                               + " join tbCompany p on c.companyId = p.id {2} "
                               + " group by ct.[name],c.CardPrice,ct.id order by ct.[name]";

            sbSql.AppendFormat(sSql

                          , this.ddlCardType.SelectedItem.Value == "" ? "" : " and ct.id = " + this.ddlCardType.SelectedItem.Value
                          , this.ddlProfessionAreaType.SelectedItem.Value == "" ? "" : "and pt.id = " + this.ddlProfessionAreaType.SelectedItem.Value
                          , this.ddlCompany.SelectedItem.Value == "" ? "" : "and c.companyid =  " + this.ddlCompany.SelectedItem.Value
                          , string.IsNullOrEmpty(this.txtBatchNumber.Text.Trim()) ? "" : " and c.cardcode like '" + this.txtBatchNumber.Text.Trim() + "%'"
                            );

            return sbSql.ToString(); ;
        }

        private void BindProfessionAreaType()
        {
            DataSet dsType = new DataSet();
            DALProfessionAreaType theType = new DALProfessionAreaType();
            if (!string.IsNullOrEmpty(base.CompanyId))
            {
                dsType = theType.GetListByCompanyId(base.CompanyId);
            }
            else
            {
                dsType = theType.GetList(string.Empty);
            }
            if (dsType.Tables.Count > 0)
            {
                this.ddlProfessionAreaType.DataSource = dsType.Tables[0].DefaultView;
                this.ddlProfessionAreaType.DataTextField = "Name";
                this.ddlProfessionAreaType.DataValueField = "Id";
                this.ddlProfessionAreaType.DataBind();
            }
            this.ddlProfessionAreaType.Items.Insert(0, new ListItem("所属专区", ""));

        }


        private void BindCompanyList()
        {
            DataSet dsType = new DataSet();
            dalCompany theType = new dalCompany();
            dsType = theType.GetList("");
            if (dsType.Tables.Count > 0)
            {
                this.ddlCompany.DataSource = dsType.Tables[0].DefaultView;
                this.ddlCompany.DataTextField = "Name";
                this.ddlCompany.DataValueField = "Id";
                this.ddlCompany.DataBind();
                this.ddlCompany.Items.Insert(0, new ListItem("请选择公司...", ""));
            }
       
            if (!string.IsNullOrEmpty(base.CompanyId))
            {
                this.ddlCompany.Items.FindByValue(base.CompanyId).Selected = true;
                this.ddlCompany.Enabled = false;
            }
        }

        private void GridViewBind(string sqlSort)
        {
            string sql = GetSql();
            if (sqlSort == "")
            {
                //sql = sql + " order by 日期 desc ";
            }
            else
            {
                sql = sql + sqlSort;
            }
            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];
            this.GridView1.DataSource = table.DefaultView;
            this.GridView1.DataBind();
            this.LblCurrentIndex.Text = "当前第 " + ((this.GridView1.PageIndex + 1)).ToString() + "页";
            this.LblPageCount.Text = "共 " + this.GridView1.PageCount.ToString() + "页";
            this.LblRecordCount.Text = "总计 " + table.Rows.Count.ToString() + "个记录";
            if (table.Rows.Count == 0)
            {
                this.btnFirst.Visible = false;
                this.btnPrev.Visible = false;
                this.btnNext.Visible = false;
                this.btnLast.Visible = false;
                this.LblCurrentIndex.Visible = false;
                this.LblPageCount.Visible = false;
                this.LblRecordCount.Visible = false;
            }
            else if (this.GridView1.PageCount == 1)
            {
                this.btnFirst.Visible = false;
                this.btnPrev.Visible = false;
                this.btnNext.Visible = false;
                this.btnLast.Visible = false;
            }
            this.btnFirst.CommandName = "1";
            this.btnPrev.CommandName = (this.GridView1.PageIndex == 0) ? "1" : this.GridView1.PageIndex.ToString();
            this.btnNext.CommandName = (this.GridView1.PageCount == 1) ? this.GridView1.PageCount.ToString() : ((this.GridView1.PageIndex + 2)).ToString();
            this.btnLast.CommandName = this.GridView1.PageCount.ToString();
            this.ddlCurrentPage.Items.Clear();
            if (this.GridView1.PageCount > 0)
            {
                for (int i = 1; i <= this.GridView1.PageCount; i++)
                {
                    this.ddlCurrentPage.Items.Add(i.ToString());
                }
                this.ddlCurrentPage.SelectedIndex = this.GridView1.PageIndex;
            }


        }




        protected void Page_Load(object sender, EventArgs e)
        {

            if (!base.IsPostBack)
            {
                if (string.IsNullOrEmpty(base.CompanyId))
                {
                    base.ValidateLoginRule(ERule.CardStat);
                }
                this.BindProfessionAreaType();
                this.BindCompanyList();
            }
        }

        protected void PagerButtonClick(object sender, EventArgs e)
        {
            this.GridView1.PageIndex = Convert.ToInt32(((LinkButton)sender).CommandName) - 1;
            this.GridViewBind("");
        }

        protected void ibtnSearch_Click(object sender, ImageClickEventArgs e)
        {
            this.GridViewBind("");
        }


        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            //if (ViewState["Ascending"] == null)
            //{
            //    ViewState["Ascending"] = " desc ";
            //    this.GridViewBind(" order by " + e.SortExpression + " asc ");
            //}
            //else
            //{
            //    this.GridViewBind(" order by " + e.SortExpression + ViewState["Ascending"].ToString());
            //    if (ViewState["Ascending"].ToString() == " asc ")
            //    {
            //        ViewState["Ascending"] = " desc ";
            //    }
            //    else
            //    {
            //        ViewState["Ascending"] = " asc ";
            //    }
            //}

        }

        protected void ibtnExport_Click(object sender, ImageClickEventArgs e)
        {
            string fileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";//设置导出文件的名称   
            string sql = GetSql();

            DataTable table = new DataTable();
            table = SQLAccess.GreatDs(sql).Tables[0];
            string sTitle = string.Empty;

            sTitle = " 卡片统计表";

            Gift.Utile.OperateExcel.ExportCSVWithTitle(table, fileName, sTitle);
        }



        //void ViewChart(DataTable dtChart)
        //{


        //    if (this.ddlTimeType.SelectedItem.Value == "2")
        //    {
        //        string sTitle = this.ddlYear.SelectedItem.Value + "年" + "卡片状态数量统计(月)";

        //        string[] xNames = new string[dtChart.Rows.Count];
        //        double[] yVal1 = new double[dtChart.Rows.Count];
        //        double[] yVal2 = new double[dtChart.Rows.Count];
        //        double[] yVal3 = new double[dtChart.Rows.Count];

        //        int m = 0;
        //        for (int i = dtChart.Rows.Count - 1; i >= 0; i--)
        //        {
        //            xNames[m] = int.Parse(dtChart.Rows[i][0].ToString().Substring(5, 2)).ToString() + "月";
        //            yVal1[m] = double.Parse(dtChart.Rows[i][2].ToString());
        //            yVal2[m] = double.Parse(dtChart.Rows[i][3].ToString());
        //            yVal3[m] = double.Parse(dtChart.Rows[i][4].ToString());
        //            m++;

        //        }

        //    }
        //    else
        //    {
        //        string sTitle = this.ddlYear.SelectedItem.Value + "年" + "卡片状态数量统计(天)";

        //        string[] xNames = new string[dtChart.Rows.Count];
        //        double[] yVal1 = new double[dtChart.Rows.Count];
        //        double[] yVal2 = new double[dtChart.Rows.Count];
        //        double[] yVal3 = new double[dtChart.Rows.Count];

        //        int m = 0;
        //        for (int i = dtChart.Rows.Count - 1; i >= 0; i--)
        //        {
        //            xNames[m] = dtChart.Rows[i][0].ToString();
        //            yVal1[m] = double.Parse(dtChart.Rows[i][2].ToString());
        //            yVal2[m] = double.Parse(dtChart.Rows[i][3].ToString());
        //            yVal3[m] = double.Parse(dtChart.Rows[i][4].ToString());
        //            m++;

        //        }

        //    }
        //}

        protected void ddlProfessionAreaType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(this.ddlProfessionAreaType.SelectedItem.Value))
            {
                DataSet dsType = new DataSet();
                DALCardType theType = new DALCardType();
                dsType = theType.GetList(" ProfessionAreaTypeId = " + this.ddlProfessionAreaType.SelectedItem.Value);
                if (dsType.Tables.Count > 0)
                {
                    this.ddlCardType.DataSource = dsType.Tables[0].DefaultView;
                    this.ddlCardType.DataTextField = "Name";
                    this.ddlCardType.DataValueField = "Id";
                    this.ddlCardType.DataBind();
                }
                this.ddlCardType.Items.Insert(0, new ListItem("所属卡片类型", ""));
            }
        }


    }
}